﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Reflection;
using Model;

namespace DAL
{
    /// <summary>
    /// 项目基本信息表
    /// </summary>
    public class Basic_InfoDal : BaseDal<Model.EABasic_Info>
    {
        public override string tablename
        {
            get { return "EABasic_Info"; }
        }

        public override Dictionary<string, bool> PKName
        {
            get
            {

                Dictionary<string, bool> dic = new Dictionary<string, bool>();
                dic.Add("Basic_id", true);
                return dic;
            }
        }


        string str = ConfigurationManager.ConnectionStrings["db"].ConnectionString;


       /// <summary>
       /// 登记列表
       /// </summary>
       /// <returns></returns>
        public List<Model.dengji> DList()
        {
            using (SqlConnection con = new SqlConnection(str))
            {
                con.Open();
                string strsql = @"select a.ProjectNumber,b.Department_Id,b.Department_Name,c.Loan_Name,
c.Loan_TEL,a.Basic_Quota,a.Basic_CreateTime,a.Basic_State,a.Survey_StartTime,a.Survey_EndTime,a.basic_UnitPrice,d.Users_Name
from EABasic_Info a join SysDepartment b
on a.The_Department=b.Department_Id join EALoan_Info c
on a.ProjectNumber=c.ProjectNumber
join  SysUser d on a.Survey=d.Users_Id";
                SqlCommand cmd = new SqlCommand(strsql, con);
                SqlDataAdapter sat = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sat.Fill(dt);
                List<Model.dengji> list = new List<Model.dengji>();
                foreach (DataRow i in dt.Rows)
                {
                    Model.dengji p = new Model.dengji();
                    p.loan_TEL = i["loan_TEL"].ToString();
                    p.department_Id = (int)i["department_Id"];
                    p.basic_Quota = Convert.ToInt32(i["basic_Quota"]);
                    p.basic_CreateTime = Convert.ToDateTime(i["basic_CreateTime"]);
                    p.basic_State = Convert.ToInt32(i["basic_State"]);
                    p.projectNumber = i["projectNumber"].ToString();
                    p.department_Name = i["department_Name"].ToString();
                    p.loan_Name = i["loan_Name"].ToString();
                    p.survey_EndTime = (DateTime)i["Survey_EndTime"];
                    p.survey_StartTime = (DateTime)i["Survey_StartTime"];
                    p.basic_UnitPrice =(decimal)i["basic_UnitPrice"];
                    p.users_Name = i["users_Name"].ToString();
                    list.Add(p);
                }
                return list;
            }
        }
        /// <summary>
        /// 修改  为勘察结束
        /// </summary>
        /// <param name="t"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateString(EABasic_Info b)
        {
            string sql = "update  EABasic_Info set Basic_State=" + b.basic_State + " where ProjectNumber='" + b.projectNumber + "'";
            return Common.DBHelp.GetExecuteNonQuery(sql);
        }
        /// <summary>
        /// 查看详情
        /// </summary>
        /// <returns></returns>
        public List<Model.dengji> BackDatas(string pnum)
        {
            string sql = @"select *
from EABasic_Info a join SysDepartment b
on a.The_Department=b.Department_Id join EALoan_Info c
on a.ProjectNumber=c.ProjectNumber
where a.ProjectNumber='"+pnum+"'";
            SqlDataReader reader = Common.DBHelp.GetReader(sql);
            List<Model.dengji> list = new List<Model.dengji>();
            while (reader.Read())
            {
                Type type = typeof(Model.dengji);
                Model.dengji obj = (Model.dengji)type.Assembly.CreateInstance(type.FullName);
                PropertyInfo[] ps = type.GetProperties();
                foreach (PropertyInfo item in ps)
                {
                    if (item.Name != "users_Name")
                    { 
                       item.SetValue(obj, reader[item.Name]);
                    } 
                }
                list.Add(obj);
            }
            return list;
        }
        /// <summary>
        /// 登记添加
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        public bool BasicAdd(EABasic_Info b)
        {
           
            string sql = @"insert into EABasic_Info (surplus_Years,basic_Quota,house_Located,
house_Purpose,house_Time,yers_Source,assessment,presentation_Type,the_Department,survey,
evaluation,basic_State,basic_CreateTime,user_Id,issue_Time,survey_StartTime,survey_EndTime,
basic_Evaluation,Basic_ValueTime,Basic_UnitPrice,Basic_Acreage,Basic_Total,Basic_Excl,Basic_Enclosure) values
 (" + b.surplus_Years + "," + b.basic_Quota + ",'"+ b.house_Located
+ "'," + b.house_Purpose + ",'" + b.house_Time + "'," + b.yers_Source + ",'" + b.assessment
+ "'," + b.presentation_Type +"," + b.the_Department +"," + b.survey + "," + b.evaluation + 
"," + b.basic_State + ",'" + b.basic_CreateTime + "'," + b.user_Id+ ",'" + b.issue_Time +
"','" + b.survey_StartTime + "','" + b.survey_EndTime + "'," + b.basic_Evaluation + ",'1990-1-1',0,0,0,'默认字段','默认字段')";

            return Common.DBHelp.GetExecuteNonQuery(sql);
        }

        /// <summary>
        /// 获取最新添加的项目编号
        /// </summary>
        /// <returns></returns>
        public string GetBasic()
        {
            string sql = "select * from [dbo].[EABasic_Info] order by ProjectNumber desc";
            return (Common.DBHelp.GetExecuteScalar(sql)).ToString();
        }
        /// <summary>
        /// 审核显示
        /// </summary>
        /// <returns></returns>
        public List<PingGu> PList()
        {
            using (SqlConnection con = new SqlConnection(str))
            {
                con.Open();
                string strsql = @"select c.Users_Name as Survey,d.Users_Name as Evaluation,Loan_Name,a.ProjectNumber,b.House_Located,Basic_UnitPrice,Basic_Acreage,Basic_Total,Basic_State from EALoan_Info a join EABasic_Info b on a.ProjectNumber=b.ProjectNumber join SysUser  c  on c.Users_Id=b.Survey join SysUser d on  d.Users_Id=b.Evaluation";
                SqlCommand cmd = new SqlCommand(strsql, con);
                SqlDataAdapter sat = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sat.Fill(dt);
                List<PingGu> list = new List<PingGu>();
                foreach (DataRow i in dt.Rows)
                {
                    PingGu pg = new PingGu();
                    pg.basic_Acreage = Convert.ToDecimal(i["basic_Acreage"]);
                    pg.basic_State = Convert.ToInt32(i["basic_State"]);
                    pg.basic_Total = Convert.ToDecimal(i["basic_Total"]);
                    pg.basic_UnitPrice = Convert.ToDecimal(i["basic_UnitPrice"]);
                    pg.evaluation = i["evaluation"].ToString();
                    pg.house_Located = i["house_Located"].ToString();
                    pg.loan_Name = i["loan_Name"].ToString();
                    pg.projectNumber = i["projectNumber"].ToString(); ;
                    pg.survey = i["survey"].ToString();
                    list.Add(pg);
                }
                return list;
            }
        }
        /// <summary>
        /// 修改项目状态
        /// </summary>
        /// <returns></returns>
        public bool UpdateState(int num,string pro) 
        {
            string sql = "update EABasic_Info set Basic_State='" + num + "' where projectNumber='"+pro+"'";
            return Common.DBHelp.GetExecuteNonQuery(sql);
        }

          /// <summary>
        /// 修改评估
        /// </summary>
        /// <returns></returns>
        public bool updPinggu(EABasic_Info b)
        {
            string sql = @"update  EABasic_Info
set basic_Evaluation="+b.basic_Evaluation+",survey_StartTime='"+b.survey_StartTime+"',survey_EndTime='"+b.survey_EndTime+"',basic_ValueTime='"+b.basic_ValueTime+"',basic_UnitPrice="+b.basic_UnitPrice+",basic_Acreage="+b.basic_Acreage+",basic_Total="+b.basic_Total+",basic_Excl='"+b.basic_Excl+"',basic_Enclosure='"+b.basic_Enclosure+"',basic_State=5 where projectNumber='"+b.projectNumber+"'";
            return Common.DBHelp.GetExecuteNonQuery(sql);
        }


         /// <summary>
        /// 导出反填
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Model.Extend.Derive>  Back(string id)
        {
            string sql = @"select distinct Loan.Loan_Name,Loan.Loan_IdCard,Dep.Department_Name,Basics.ProjectNumber,
Basics.Issue_Time,Basics.Basic_CreateTime,Basics.Basic_Acreage,Basics.Surplus_Years,
Basics.Basic_Quota,Dic.Dictionary_Name,Floors.Floor_Address
 from [dbo].[EALoan_Info] Loan join [dbo].[EABasic_Info] Basics 
on Loan.ProjectNumber=Basics.ProjectNumber
join [dbo].[SysDepartment] Dep on Basics.The_Department=Dep.Department_Id
join [dbo].[EAFloors] Floors on Basics.ProjectNumber=Floors.ProjectNumber
join [dbo].[SysDictionary]Dic on Basics.House_Purpose=Dic.Dictionary_Number  
where Basics.ProjectNumber='"+id+"'";

            SqlDataReader reader = Common.DBHelp.GetReader(sql);
            List<Model.Extend.Derive> list = new List<Model.Extend.Derive>();
            while (reader.Read())
            {
                Type type = typeof(Model.Extend.Derive);
                Model.Extend.Derive obj = (Model.Extend.Derive)type.Assembly.CreateInstance(type.FullName);
                PropertyInfo[] ps = type.GetProperties();
                foreach (PropertyInfo item in ps)
                {
                    item.SetValue(obj, reader[item.Name]);
                }
                list.Add(obj);
            }
            return list;
        }
        /// <summary>
        /// 反填
        /// </summary>
        /// <param name="projectNumber"></param>
        /// <returns></returns>
        public Model.EABasic_Info Edit(string projectNumber)
        {
            string sql = "select * from dbo.EABasic_Info a join dbo.EALoan_Info B on a.ProjectNumber=b.ProjectNumber where a.ProjectNumber='" + projectNumber + "'";
            DataTable dt = Common.DBHelp.GetDataSet(sql);
            Model.EABasic_Info model = new EABasic_Info();
            foreach (DataRow item in dt.Rows)
            {
                model.loan_Name = item["loan_Name"].ToString();
                model.loan_IdCard = item["loan_IdCard"].ToString();
                model.loan_TEL = item["loan_TEL"].ToString();
                model.loan_Contacts = item["loan_Contacts"].ToString();
                model.loan_ContactsTEL = item["loan_ContactsTEL"].ToString();
                model.surplus_Years = (int)item["surplus_Years"];
                model.basic_Quota = (decimal)item["basic_Quota"];
                model.house_Located = item["house_Located"].ToString();
                model.house_Purpose = (int)item["house_Purpose"];
                model.house_Time = item["house_Time"].ToString();
                model.yers_Source = (int)item["yers_Source"];
                model.presentation_Type = (int)item["presentation_Type"];
                model.the_Department = (int)item["the_Department"];
                model.survey = (int)item["survey"];
                model.evaluation = (int)item["evaluation"];
                model.issue_Time = (DateTime)item["issue_Time"];
                model.assessment = item["assessment"].ToString();
                
            }
            return model;
        }
        /// <summary>
        /// 更换评估员
        /// </summary>
        /// <param name="projectNumber">项目号</param>
        /// <param name="ID">评估员编号</param>
        /// <returns></returns>
        public bool UpdateEvaluation(string projectNumber, int ID)
        {
            string sql = "update EABasic_Info set evaluation=" + ID + " where projectNumber='" + projectNumber + "'";
            return Common.DBHelp.GetExecuteNonQuery(sql);
        }
        /// <summary>
        /// 更改数据
        /// </summary>
        /// <param name="projectNumber">项目号</param>
        /// <param name="ID">评估员</param>
        /// <param name="Bumen">部门</param>
        /// <param name="Kancha">勘察员</param>
        /// <returns></returns>
        public bool UpdateEvaluation(string projectNumber, int ID,int Bumen,int Kancha )
        {
            string sql = "update EABasic_Info set evaluation=" + ID + " ,The_Department=" + Bumen + " ,Survey=" + Kancha + "  where projectNumber='" + projectNumber + "'";
            return Common.DBHelp.GetExecuteNonQuery(sql);
        }
    }
}
